package com.dingding.order.core.special.sql;

/**
 * @Description:专车订单的sql
 * @author fengshuonan
 * @date 2016年8月19日 下午12:38:49
 * @version 叮叮搭车
 * @since 2.4
 */
public class SpecialOrderSql {

	//获取所有的车主地址信息
	public static final String GET_ALL_DRIVERS = "SELECT s.userId AS driverId,s.latitude AS latitude,s.longitude AS longitude FROM SpecialUserPosition s inner join specialuservehicleinfo v on s.userId = v.userId where s.currentCity like ? AND s.supStatus = 1 AND v.authType = ?";

	//获取专车进行中订单
	public static final String GET_UNDER_WAY_ORDER = "SELECT s.totalMoney AS totalMoney,s.totalMoney AS orderCost, " +
			"s.placeOrderUserId AS placeOrderUserId,s.grabOrderUserId AS grabOrderUserId, " +
			"s.createTime AS creatTime,s.distance AS distance,s.endAddress AS endAddress, " +
			"s.endLatitude AS endLatitude,s.endLongitude AS endLongitude, " +
			"s.startAddress AS startAddress,s.startLatitude AS startLatitude,s.startLongitude AS startLongitude, " +
			"vi.lisencePlate AS licensePlate,ud.nickName AS nickName,ud.loginId AS otherUserId, " +
			"s.specialOrderId AS oid,s.orderStatus AS orderStatus,s.setOutTime AS setOutTime, " +
			"ud.sex AS sex,ud.tellphone AS telePhone,vi.vehicleColor AS vehicleColor,vi.vehicleModel AS vehicleModel,vi.vehicleType AS vehicleType, " +
			"ud.userAvatar AS userAvatar,ud.userTabloidAvatar AS userTabloidAvatar " +
			"FROM userspecials s  " +
			"LEFT JOIN userdetail ud ON ud.loginId = ( " +
			"	CASE  " +
			"	WHEN s.placeOrderUserId = ? THEN s.grabOrderUserId " +
			"	WHEN s.grabOrderUserId = ? THEN s.placeOrderUserId " +
			"	ELSE s.placeOrderUserId END) " +
			"LEFT JOIN vehicleinfo vi ON vi.loginId = s.grabOrderUserId " +
			"WHERE (s.placeOrderUserId = ? OR s.grabOrderUserId = ?)  " +
			"AND s.orderStatus IN (1,2,3,4) " +
			"ORDER BY s.createTime desc";
	
	/**
	 * 
	 * @Title: GET_UNDER_WAY_ORDER 
	 * @Description: 获取进行中订单
	 * @param @return    设定文件 
	 * @author YHQ 
	 * @return String    返回类型
	 */
	public static String getUnderWayOrder(Integer clientType){
		
		StringBuffer sBuffer = new StringBuffer();
		
		sBuffer.append("SELECT s.totalMoney AS totalMoney,s.totalMoney AS orderCost, s.placeOrderUserId AS placeOrderUserId,s.grabOrderUserId AS grabOrderUserId, s.createTime AS creatTime,s.distance AS distance,s.endAddress AS endAddress, s.endLatitude AS endLatitude,s.endLongitude AS endLongitude, s.startAddress AS startAddress,s.startLatitude AS startLatitude,s.startLongitude AS startLongitude, vi.lisencePlate AS licensePlate,ud.nickName AS nickName, s.specialOrderId AS oid,s.orderStatus AS orderStatus,s.setOutTime AS setOutTime, ud.sex AS sex,vi.vehicleColor AS vehicleColor,vi.vehicleModel AS vehicleModel,vi.vehicleType AS vehicleType, ud.userAvatar AS userAvatar,ud.userTabloidAvatar AS userTabloidAvatar,ud.userLevel,s.estimateDistance,s.estimateMoney,s.publishType");
		
		if(clientType == 1){	//专车司机端
			
			sBuffer.append(",ud.loginId AS otherUserId");
			sBuffer.append(",ud.tellphone AS telePhone");
			sBuffer.append(" FROM userspecialordert s");
			sBuffer.append(" LEFT JOIN userdetail ud ON ud.loginId = s.placeOrderUserId");
			sBuffer.append(" LEFT JOIN vehicleinfo vi ON vi.loginId = s.grabOrderUserId");
			sBuffer.append(" WHERE s.grabOrderUserId = ?");
		}else if(clientType == 2){ // 专车乘客端
			
			sBuffer.append(",ud.userId AS otherUserId");
			sBuffer.append(",ud.telPhone AS telePhone");
			sBuffer.append(",d.vehicleLevelName vehicleLevelName");
			sBuffer.append(" FROM dd_vehicle_level d");
			sBuffer.append(",userspecialordert s");
			sBuffer.append(" LEFT JOIN specialuserinfo ud ON ud.userId = s.grabOrderUserId");
			sBuffer.append(" LEFT JOIN specialuservehicleinfo vi ON vi.userId = s.grabOrderUserId");
			sBuffer.append(" WHERE s.placeOrderUserId = ?");
			sBuffer.append(" AND d.vehicleLevelId = s.vehicleLevelId");
		}
		
		sBuffer.append(" AND s.orderStatus IN(1,2,3,4) ORDER BY s.createTime desc");
		
		return sBuffer.toString();
	}
	
	// 获取出租车进行中订单(乘客端的进行中订单)
	public static String GET_UNDER_WAY_TAXI_ORDER = "SELECT s.totalMoney AS totalMoney,s.totalMoney AS orderCost,s.placeOrderUserId AS placeOrderUserId,s.grabOrderUserId AS grabOrderUserId,s.createTime AS creatTime,  s.distance AS distance,s.endAddress AS endAddress,  s.endLatitude AS endLatitude,s.endLongitude AS endLongitude,  s.startAddress AS startAddress,s.startLatitude AS startLatitude,  s.startLongitude AS startLongitude,vi.lisencePlate AS licensePlate,  ud.nickName AS nickName,s.taxiOrdertId AS oid,s.orderStatus AS orderStatus,s.setOutTime AS setOutTime,ud.sex AS sex,vi.vehicleColor AS vehicleColor,vi.vehicleModel AS vehicleModel,vi.vehicleType AS vehicleType,ud.userAvatar AS userAvatar,ud.userTabloidAvatar AS userTabloidAvatar,ud.userLevel,ud.userId AS otherUserId,ud.telPhone AS telePhone FROM usertaxiordert s LEFT JOIN specialuserinfo ud ON ud.userId = s.placeOrderUserId LEFT JOIN specialuservehicleinfo vi ON vi.userId = s.grabOrderUserId WHERE s.grabOrderUserId = ? AND s.orderStatus IN(1,2,3,4) ORDER BY s.createTime DESC";
	
	// 获取出租车进行中订单(司机端的进行中订单)
	public static String GET_DRIVER_UNDER_WAY_TAXI_ORDER = "SELECT s.totalMoney AS totalMoney,s.totalMoney AS orderCost,s.placeOrderUserId AS placeOrderUserId,s.grabOrderUserId AS grabOrderUserId,s.createTime AS creatTime,  s.distance AS distance,s.endAddress AS endAddress,  s.endLatitude AS endLatitude,s.endLongitude AS endLongitude,  s.startAddress AS startAddress,s.startLatitude AS startLatitude,  s.startLongitude AS startLongitude,vi.lisencePlate AS licensePlate,  ud.nickName AS nickName,s.taxiOrdertId AS oid,s.orderStatus AS orderStatus,s.setOutTime AS setOutTime,ud.sex AS sex,vi.vehicleColor AS vehicleColor,vi.vehicleModel AS vehicleModel,vi.vehicleType AS vehicleType,ud.userAvatar AS userAvatar,ud.userTabloidAvatar AS userTabloidAvatar,ud.userLevel,ud.loginId AS otherUserId,ud.tellphone AS telePhone FROM usertaxiordert s LEFT JOIN userdetail ud ON ud.loginId = s.placeOrderUserId LEFT JOIN specialuservehicleinfo vi ON vi.userId = s.grabOrderUserId WHERE s.grabOrderUserId = ? AND s.orderStatus IN(1,2,3,4) ORDER BY s.createTime DESC";
	
	
	//获取专车的历史订单
	public static String getSpecialHistoryOrder(Integer userType){
		
		StringBuilder sBuilder = new StringBuilder();
		
		sBuilder.append("SELECT o.cancelType AS cancelType,o.createTime AS createTime,o.distance AS distance,o.endAddress AS endAddress,o.isComment AS isComment,ud.nickName AS nickName,o.specialOrderId AS oid,o.totalMoney AS orderCost,o.publishType AS publishType,o.setOutTime AS setOutTime,o.startAddress AS startAddress,o.totalMoney AS totalMoney,ud.userAvatar AS userAvatar,ud.userLevel AS userLevel,ud.userTabloidAvatar AS userTabloidAvatar,o.orderStatus FROM userspecialorder o");
		
		
		if(userType == 1){
			sBuilder.append(" LEFT JOIN userdetail ud ON ud.loginId = o.placeOrderUserId");
			sBuilder.append(" WHERE o.grabOrderUserId = ?");
		}if(userType == 2){
			sBuilder.append(" LEFT JOIN specialuserinfo ud ON ud.userId = o.grabOrderUserId");
			sBuilder.append(" WHERE o.placeOrderUserId = ?");
		}
		
		sBuilder.append(" ORDER BY o.createTime desc");
		
		return sBuilder.toString();
	}
	
	//获取专车的历史订单
	public static String getSpecialMergeHistoryOrder(Integer userType){
		
		StringBuilder sBuilder = new StringBuilder();
		
		sBuilder.append("SELECT o.cancelType AS cancelType,o.createTime AS createTime,o.distance AS distance,o.endAddress AS endAddress,o.isComment AS isComment,ud.nickName AS nickName,o.specialOrderId AS oid,o.totalMoney AS orderCost,o.publishType AS publishType,o.setOutTime AS setOutTime,o.startAddress AS startAddress,o.totalMoney AS totalMoney,ud.userAvatar AS userAvatar,ud.userLevel AS userLevel,ud.userTabloidAvatar AS userTabloidAvatar,o.orderStatus FROM userspecialorder o");
		
		
		if(userType == 1){
			sBuilder.append(" LEFT JOIN userdetail ud ON ud.loginId = o.placeOrderUserId");
			sBuilder.append(" WHERE o.grabOrderUserId = ?");
		}if(userType == 2){
			sBuilder.append(" LEFT JOIN specialuserinfo ud ON ud.userId = o.grabOrderUserId");
			sBuilder.append(" WHERE o.placeOrderUserId = ?");
		}
		
		sBuilder.append(" ORDER BY o.createTime desc");
		
		return sBuilder.toString();
	}
	
	
}
